program prepare_constrained_table	
	collapse (count) countobs=DTV_final if htb_dummy, ///
		by(LTV_constrained`1' LTI_constrained)
	egen tot = sum(countobs)
	gen pct = countobs / tot * 100
	drop tot
	reshape wide countobs pct, i(LTV_constrained`1') j(LTI_constrained)
	
	format countobs* %7.0fc
	format pct* %2.0f
	
	/* 
	gen _varname = "CLTV $\leq$ `1'\%" if LTV_constrained`1' == 0
	replace _varname = "CLTV $>$ `1'\%" if LTV_constrained`1' == 1
	order _varname
	*/
	drop LTV_constrained`1'
	
	tostring pct*, force usedisplayformat replace
	replace pct0 = pct0 + "\%"
	replace pct1 = pct1 + "\%"
end



use "0 - Shared Appreciation\data\HTB_main", replace


*------------------------------------------------------------------------------*
* Constrained households *
*------------------------------------------------------------------------------*
	

*--- Definitions
gen LTV_constrained95 	= DTV_final < 5 
gen LTV_constrained90 	= DTV_final < 10 

gen LTI_constrained = combined_LTI > 4.5


*--- Tables
foreach i in 90 95 {	
	preserve	
		prepare_constrained_table `i'
		add_parenthesis pct*
		latexTable
		outsheet using "${projectdir}\table\tabconstr`i'.tex", ///
			replace noquote nonames
	restore

	preserve	
		keep if advtype == 3	// First time buyers
		prepare_constrained_table `i'
		add_parenthesis pct*
		latexTable
		capture drop _varname
		outsheet using "${projectdir}\table\tabconstr`i'_ftb.tex", ///
			replace noquote nonames
	restore
}

	
*------------------------------------------------------------------------------*
* LTI *
*------------------------------------------------------------------------------*


bys htb_dummy: gen tmp 								= _N

gen LTI_roundup50pct	 				 			= 0.10 * ceil(LTI_original/0.10)
bys LTI_roundup50pct htb_dummy: gen tmp1 			= _N
replace tmp1 										= 100 * tmp1/tmp
bys LTI_roundup50pct htb_dummy: gen fo_tmp1 		= (_n==1)

gen LTI_combined_roundup50pct 						= 0.10 * ceil(combined_LTI/0.10)
bys LTI_combined_roundup50pct htb_dummy: gen tmp2 	= _N
replace tmp2 										= 100 * tmp2/tmp
bys LTI_combined_roundup50pct htb_dummy: gen fo_tmp2= (_n==1)

			
		twoway ///
				(connected tmp1 LTI_roundup50pct 			if fo_tmp1==1 & htb_dummy==1 & LTI_roundup50pct < 5, msize(medsmall) sort ) /// 
				(connected tmp2 LTI_combined_roundup50pct 	if fo_tmp2==1 & htb_dummy==1 & LTI_combined_roundup50pct < 7, msize(medsmall) msymbol(X) sort ), ///
				xline(/*4*/ 4.5, lwidth(vthin) lcolor(black) lpattern(dash)) ///
				ytitle("Share of Mortgages (%)") ///
				xtitle("Loan-To-Income") ///
				xscale(range(0(1)7)) xlabel(0(1)7) xmtick(0(0.5)7)  ///
				yscale(range(0(1)5)) ylabel(0(1)5, angle(0)) ymtick(0(1)5)  ///
				legend(order(1 "Loan-To-Income" 2 "Loan + Equity-To-Income") pos(11) ring(0) col(1) size(small))	///
				graphregion(color(white)) bgcolor(white) ///
				name(gr_LTI_HTB, replace)

		twoway ///
				(connected tmp1 LTI_roundup50pct 			if fo_tmp1==1 & htb_dummy==0 & LTI_roundup50pct < 5, msize(medsmall) sort ), /// 
				xline(/*4*/ 4.5, lwidth(vthin) lcolor(black) lpattern(dash)) ///		
				ytitle("Share of Mortgages (%)") ///
				xtitle("Loan-To-Income") ///
				xscale(range(0(1)7)) xlabel(0(1)7) xmtick(0(0.5)7)  ///
				yscale(range(0(1)5)) ylabel(0(1)5, angle(0)) ymtick(0(1)5)  ///
				legend(order(1 "Loan-To-Income" 2 "Loan + Equity-To-Income") pos(11) ring(0) col(1) size(small))	///
				graphregion(color(white)) bgcolor(white) ///
				name(gr_LTI_NOHTB, replace)	

drop *tmp*



*------------------------------------------------------------------------------*
* LTV *
*------------------------------------------------------------------------------*

bys htb_dummy: gen tmp 								= _N

gen LTV_roundup50pct	 				 			= round(LTV_original) 
bys LTV_roundup50pct htb_dummy: gen tmp1 			= _N
replace tmp1 										= 100 * tmp1/tmp
bys LTV_roundup50pct htb_dummy: gen fo_tmp1 		= (_n==1)

gen LTV_combined_roundup50pct 						= round(combined_LTV) 
bys LTV_combined_roundup50pct htb_dummy: gen tmp2 	= _N
replace tmp2										= 100 * tmp2/tmp
bys LTV_combined_roundup50pct htb_dummy: gen fo_tmp2= (_n==1)


		
	twoway ///
			(connected tmp1 LTV_roundup50pct 			if fo_tmp1==1 & htb_dummy==1 & LTV_roundup50pct > 50 & LTV_roundup50pct < 100, msize(medsmall) sort ) /// 
			(connected tmp2 LTV_combined_roundup50pct 	if fo_tmp2==1 & htb_dummy==1 & LTV_roundup50pct > 50 & LTV_combined_roundup50pct < 100, msize(medsmall) msymbol(X) sort ), ///
			xline(60 70 75 80 85 90 95, lwidth(vthin) lcolor(black) lpattern(dash)) ///
			ytitle("Share of Mortgages (%)") ///
			xtitle("Loan-To-Value (%)") ///
			xscale(range(50(1)100)) xlabel(55(10)95) xmtick(50(1)100)  ///
			yscale(range(0(20)60)) ylabel(0(20)60, angle(0)) ymtick(0(20)60)  ///
			legend(order(1 "Loan-To-Value" 2 "Loan + Equity-To-Value") pos(11) ring(0) col(1) size(small))	///
			graphregion(color(white)) bgcolor(white) ///
			name(gr_LTV_HTB, replace)

	twoway ///
			(connected tmp1 LTV_roundup50pct 			if fo_tmp1==1 & htb_dummy==0 & LTV_roundup50pct > 50 & LTV_roundup50pct < 100, msize(medsmall) sort ), /// 
			xline(60 70 75 80 85 90 95, lwidth(vthin) lcolor(black) lpattern(dash)) ///
			ytitle("Share of Mortgages (%)") ///
			xtitle("Loan-To-Value (%)") ///
			xscale(range(50(1)100)) xlabel(55(10)95) xmtick(50(1)100)  ///
			yscale(range(0(20)60)) ylabel(0(20)60, angle(0)) ymtick(0(20)60)  ///
			legend(order(1 "Loan-To-Value" 2 "Loan + Equity-To-Value") pos(11) ring(0) col(1) size(small))	///
			graphregion(color(white)) bgcolor(white) ///
			name(gr_LTV_NOHTB, replace)
			
drop *tmp*

	
	
*------------------------------------------------------------------------------*
* PTI(net) *
*------------------------------------------------------------------------------*


bys htb_dummy: gen tmp 								= _N

gen 	combined_payment = (loan_val + equity) * ( (interest00 +0.02)/12 * (1+(interest00+0.02)/12)^(mortgage_term * 12) ) / ((1+(interest00+0.02)/12)^(mortgage_term * 12) - 1) 		
gen 	combined_PTInet = combined_payment / (net_income/12) * 100 if htb_dummy==1
replace combined_PTInet = PTInet if htb_dummy==0

gen 	stress_payment = (loan_val) * ( (interest00 +0.03)/12 * (1+(interest00+0.03)/12)^(mortgage_term * 12) ) / ((1+(interest00+0.03)/12)^(mortgage_term * 12) - 1) 				
gen 	stress_PTInet 	= stress_payment / (net_income/12) * 100

gen 	combinedstress_payment = (loan_val + equity) * ( (interest00 +0.05)/12 * (1+(interest00+0.05)/12)^(mortgage_term * 12) ) / ((1+(interest00+0.05)/12)^(mortgage_term * 12) - 1) 		
gen 	combinedstress_PTInet = combinedstress_payment / (net_income/12) * 100 if htb_dummy==1
replace combinedstress_PTInet = stress_PTInet if htb_dummy==0


gen PTInet_roundup50pct	 				 			= round(PTInet)
bys PTInet_roundup50pct htb_dummy: gen tmp1 		= _N
replace tmp1 										= 100 * tmp1/tmp
bys PTInet_roundup50pct htb_dummy: gen fo_tmp1 		= (_n==1)

gen PTInet_combined_roundup50pct 						= round(combined_PTInet) 
bys PTInet_combined_roundup50pct htb_dummy: gen tmp2	= _N
replace tmp2											= 100 * tmp2/tmp
bys PTInet_combined_roundup50pct htb_dummy: gen fo_tmp2	= (_n==1)


gen PTInet_stress_roundup50pct 							= round(stress_PTInet) 
bys PTInet_stress_roundup50pct htb_dummy: gen tmp3		= _N
replace tmp3											= 100 * tmp3/tmp
bys PTInet_stress_roundup50pct htb_dummy: gen fo_tmp3	= (_n==1)

gen PTInet_combstress_roundup50pct 							= round(combinedstress_PTInet) 
bys PTInet_combstress_roundup50pct htb_dummy: gen tmp4		= _N
replace tmp4												= 100 * tmp4/tmp
bys PTInet_combstress_roundup50pct htb_dummy: gen fo_tmp4	= (_n==1)

		
		
	twoway ///
			(connected tmp1 PTInet_roundup50pct 				if fo_tmp1==1 & htb_dummy==1 & PTInet_roundup50pct<70, msize(medsmall) sort ) /// 
			(connected tmp3 PTInet_stress_roundup50pct 			if fo_tmp3==1 & htb_dummy==1 & PTInet_stress_roundup50pct<70 , msize(medsmall) sort msymbol(X) lcolor(navy) mcolor(navy)) /// 
			(connected tmp2 PTInet_combined_roundup50pct 		if fo_tmp2==1 & htb_dummy==1 & PTInet_combined_roundup50pct<70, msize(medsmall) sort lcolor(maroon) mcolor(maroon) ) ///
			(connected tmp4 PTInet_combstress_roundup50pct 		if fo_tmp4==1 & htb_dummy==1 & PTInet_combstress_roundup50pct<70, msize(medsmall) sort msymbol(X) lcolor(maroon) mcolor(maroon)) , ///
			xline(10 20 30 40 50 60, lwidth(vthin) lcolor(black) lpattern(dash)) ///		
			xtitle("Payment-To-Net Income (%)") ///
			xscale(range(0(1)70)) xlabel(0(5)70) xmtick(0(1)70)  ///
			yscale(range(0(1)4)) ylabel(0(1)4) ymtick(0(1)4)  ///
			legend(order(1 "Payment-To-Income" 2 "Stress tested" 3 "Payment + Equity-To-Income"  4 "Stress tested" ) pos(1) ring(0) col(1) size(small))	///
			graphregion(color(white)) bgcolor(white) ///
			name(gr_PTInet_HTB, replace)

	twoway ///
			(connected tmp1 PTInet_roundup50pct 			if fo_tmp1==1 & htb_dummy==0 & PTInet_roundup50pct<70, msize(medsmall) sort ) ///
			(connected tmp3 PTInet_stress_roundup50pct 		if fo_tmp3==1 & htb_dummy==0 & PTInet_stress_roundup50pct<70, msize(medsmall) msymbol(X) lcolor(navy) mcolor(navy) sort ), /// 
			xline(10 20 30 40 50 60, lwidth(vthin) lcolor(black) lpattern(dash)) ///
			ytitle("Share of Mortgages (%)") ///
			xtitle("Payment-To-Net Income (%)") ///
			xscale(range(0(1)70)) xlabel(0(5)70) xmtick(0(1)70)  ///
			yscale(range(0(1)4)) ylabel(0(1)4) ymtick(0(1)4)  ///
			legend(order(1 "Payment-To-Income" 2 "Stress tested") pos(1) ring(0) col(1) size(small))	///
			graphregion(color(white)) bgcolor(white) ///
			name(gr_PTInet_NOHTB, replace)
			
drop *tmp*
		
	
*------------------------------------------------------------------------------*
* Maturity *
*------------------------------------------------------------------------------*

bys htb_dummy: gen tmp 								= _N

gen Mat_roundup50pct	 				 			= round(mortgage_term) 
bys Mat_roundup50pct htb_dummy: gen tmp1 			= _N
replace tmp1 										= 100 * tmp1/tmp
bys Mat_roundup50pct htb_dummy: gen fo_tmp1 		= (_n==1)


	twoway ///
			(connected tmp1 Mat_roundup50pct 			if fo_tmp1==1 & htb_dummy==1 & Mat_roundup50pct < 40, msize(medsmall) sort ), ///
			xline(10 15 20 25 30 35, lwidth(vthin) lcolor(black) lpattern(dash)) ///
			ytitle("Share of Mortgages (%)") ///
			xtitle("Maturity") ///
			xscale(range(0(1)40)) xlabel(0(5)40) xmtick(0(1)40)  ///
			yscale(range(0(10)30)) ylabel(0(10)30) ymtick(0(10)30)  ///
			graphregion(color(white)) bgcolor(white) ///
			name(gr_Mat_HTB, replace)

	twoway ///
			(connected tmp1 Mat_roundup50pct 			if fo_tmp1==1 & htb_dummy==0 & Mat_roundup50pct < 40, msize(medsmall) sort ), /// 
			xline(10 15 20 25 30 35, lwidth(vthin) lcolor(black) lpattern(dash)) ///
			ytitle("Share of Mortgages (%)") ///
			xtitle("Maturity") ///
			xscale(range(0(1)40)) xlabel(0(5)40) xmtick(0(1)40)  ///
			yscale(range(0(10)30)) ylabel(0(10)30) ymtick(0(10)30)  ///
			graphregion(color(white)) bgcolor(white) ///
			name(gr_Mat_NOHTB, replace)
			
drop *tmp*

	
	
*------------------------------------------------------------------------------*
* Distributions *
*------------------------------------------------------------------------------*

*--- Paper	
gr combine gr_LTV_HTB gr_LTI_HTB, graphregion(color(white)) xsize(8) ///
	scale(1.075)
graph export "0 - Shared Appreciation\Figure\kdensity_LTV_LTI_HTB.pdf", ///
	as(pdf) replace
			
gr combine gr_LTV_NOHTB gr_LTI_NOHTB, graphregion(color(white))  xsize(8) ///
	scale(1.075)
graph export "0 - Shared Appreciation\Figure\kdensity_LTV_LTI_NOHTB.pdf", ///
	as(pdf) replace

gr combine gr_PTInet_HTB gr_Mat_HTB, graphregion(color(white))  xsize(8)  
graph export "0 - Shared Appreciation\Figure\kdensity_PTI_Mat_HTB.pdf", ///
	as(pdf) replace

gr combine gr_PTInet_NOHTB gr_Mat_NOHTB, graphregion(color(white))  xsize(8)
graph export "0 - Shared Appreciation\Figure\kdensity_PTI_Mat_NOHTB.pdf", ///
	as(pdf) replace